import os
import xlrd
import sys
import django

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")
django.setup()
from apps.czb.models import Orders
from django.db.models import Q


def insert(xlsx):
    data = xlrd.open_workbook(xlsx)  # 打开excel
    table = data.sheets()[0]
    rows = table.nrows  # 行数
    order_id_list = []
    order_data_list = []
    order_ids = []
    refund_order_id = []
    orderSn_list = []
    for i in range(1, rows):
        row_values = table.row_values(i)  # 某一行数据
        order_id = row_values[4]  # 订单号
        order_ids.append(order_id)
        orderSn = row_values[5]  # 支付识别号
        order_result = row_values[6]  # 订单状态
        orderSn_list.append(orderSn)
        if order_result == "已退款":
            refund_order_id.append(order_id)
        try:
            instance = Orders.objects.get(Q(orderId=order_id))
            if instance.result == 1:
                pass
            else:
                data = dict()
                data["result"] = instance.result
                data["pay_time"] = instance.pay_time.strftime("%Y-%m-%d %H:%M:%S")
                data["id"] = instance.id
                data["order_id"] = order_id
                data["orderSn"] = orderSn
                order_data_list.append(data)
        except Exception as e:
            # raise Exception(order_id, orderSn)
            order_id_list.append(order_id)
    print(orderSn_list)
    print(order_id_list)
    print(order_data_list)
    print(order_ids)
    print(refund_order_id)


if __name__ == '__main__':
    insert("youpinyff.xlsx")  # 优品预付费
    # insert("tongliant+1.xlsx")  # 通联t+1
    # insert("youpint+1.xlsx")  # 优品t+1
